/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.seari.utils;

import com.seari.utils.excel.ImportExcelUtil;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * @author Rainbow
 */
public class ModbusServerExcelHelper2
{
    String viewName = "View1";
    String sourceFile = "";
    int rtuNum = 6;
    int nameStart = 1;
    int nameCount = 65000;
    int addressStart = 311039;
    String systemPrefix = "";
    String suffix = "";
    String bit16 = "4F413A4D4F44000401000000";
    String bit32 = "4F413A4D4F44000402000000";
    String fileName = "aaa";
    List<String> valList2;
    boolean breakFlag = false;
    public static void main(String[] args) throws FileNotFoundException, IOException, Exception
    {
        
        ModbusServerExcelHelper2 mseh2 = new ModbusServerExcelHelper2();
        List<List<Object>> valList = new ImportExcelUtil().getBankListByExcel(new FileInputStream(new File("C:\\Users\\Rainbow\\Desktop\\20180309\\SUN2.xlsx")), "SUN2.xlsx");
        mseh2.valList2 = new ArrayList<>();
        for (List<Object> list : valList)
        {
            mseh2.valList2.add(list.get(1).toString());
        }
        System.out.println(mseh2.valList2.size());
        for(int i = 0 ; i < mseh2.valList2.size() ; i++)
        {
            if(mseh2.breakFlag)
            {
                break;
            }
            if((i % mseh2.nameCount) == 0)
            {
                Workbook wb = mseh2.generateExcel(i);
                wb.write(new FileOutputStream("d:/" + mseh2.fileName + i + ".xlsx"));
                wb.close();
                mseh2.rtuNum++;
            }
        }
    }
    
    public Workbook generateExcel(int start)
    {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();
        
        int rowNum = generateTableHead(sheet);
        rowNum = generateCNSHead(sheet, rowNum);
        rowNum = generateTableBody(sheet,rowNum,start);
        //generateTableBody(sheet, rowNum);
        return wb;
    }
    
    public int generateTableHead(Sheet sheet)
    {
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("# ascii dump of database");
        sheet.createRow(1);//空行
        return 2;
    }
    
    public int generateCNSHead(Sheet sheet,int rowNum)
    {
        //表头
        Row row = sheet.createRow(rowNum);
        row.createCell(0).setCellValue("# CNS");
        rowNum++;
        Row newRow = sheet.createRow(rowNum);//空行
        newRow.createCell(0).setCellValue("CNSpath");
        newRow.createCell(1).setCellValue("name");
        newRow.createCell(2).setCellValue("displaySeparators");
        newRow.createCell(3).setCellValue("displayNames");
        newRow.createCell(4).setCellValue("data");
        newRow.createCell(5).setCellValue("CNSDataIdentifierType");
        newRow.createCell(6).setCellValue("UserData");
        newRow.createCell(7).setCellValue("ViewId");
        rowNum++;
        //表体，前6行为固定格式
        //第一行
        Row tableBodyRow1 = sheet.createRow(rowNum);
        tableBodyRow1.createCell(0);
        tableBodyRow1.createCell(1).setCellValue(viewName);
        tableBodyRow1.createCell(2).setCellValue("lt:2 LANG:10001 \".\" LANG:10004 \".\" ");
        tableBodyRow1.createCell(3).setCellValue("lt:2 LANG:10001 \"New view 1\" LANG:10004 \"New view 1\" ");
        tableBodyRow1.createCell(4);
        tableBodyRow1.createCell(5);
        tableBodyRow1.createCell(6);
        tableBodyRow1.createCell(7).setCellValue(1);
        rowNum++;
        //第二行
        Row tableBodyRow2 = sheet.createRow(rowNum);
        tableBodyRow2.createCell(0).setCellValue("." + viewName + ":");
        tableBodyRow2.createCell(1).setCellValue(rtuNum);
        tableBodyRow2.createCell(2);
        tableBodyRow2.createCell(3).setCellValue("lt:2 LANG:10001 \"RTU1\" LANG:10004 \"RTU1\" ");
        tableBodyRow2.createCell(4).setCellValue("720896 0:0.0");
        tableBodyRow2.createCell(5).setCellValue(502);
        tableBodyRow2.createCell(6);
        tableBodyRow2.createCell(7);
        rowNum++;
        //第三行
        Row tableBodyRow3 = sheet.createRow(rowNum);
        tableBodyRow3.createCell(0).setCellValue("." + viewName + ":" + rtuNum);
        tableBodyRow3.createCell(1).setCellValue(1);
        tableBodyRow3.createCell(2);
        tableBodyRow3.createCell(3).setCellValue("lt:2 LANG:10001 \"1 InputDiscretes\" LANG:10004 \"1 InputDiscretes\" ");
        tableBodyRow3.createCell(4).setCellValue("720896 0:0.0");
        tableBodyRow3.createCell(5).setCellValue(502);
        tableBodyRow3.createCell(6);
        tableBodyRow3.createCell(7);
        rowNum++;
        //第四行
        Row tableBodyRow4 = sheet.createRow(rowNum);
        tableBodyRow4.createCell(0).setCellValue("." + viewName + ":" + rtuNum);
        tableBodyRow4.createCell(1).setCellValue(2);
        tableBodyRow4.createCell(2);
        tableBodyRow4.createCell(3).setCellValue("lt:2 LANG:10001 \"2 Coils\" LANG:10004 \"2 Coils\" ");
        tableBodyRow4.createCell(4).setCellValue("720896 0:0.0");
        tableBodyRow4.createCell(5).setCellValue(502);
        tableBodyRow4.createCell(6);
        tableBodyRow4.createCell(7);
        rowNum++;
        //第五行
        Row tableBodyRow5 = sheet.createRow(rowNum);
        tableBodyRow5.createCell(0).setCellValue("." + viewName + ":" + rtuNum);
        tableBodyRow5.createCell(1).setCellValue(3);
        tableBodyRow5.createCell(2);
        tableBodyRow5.createCell(3).setCellValue("lt:2 LANG:10001 \"3 InputRegisters\" LANG:10004 \"3 InputRegisters\" ");
        tableBodyRow5.createCell(4).setCellValue("720896 0:0.0");
        tableBodyRow5.createCell(5).setCellValue(502);
        tableBodyRow5.createCell(6);
        tableBodyRow5.createCell(7);
        rowNum++;
        //第六行
        Row tableBodyRow6 = sheet.createRow(rowNum);
        tableBodyRow6.createCell(0).setCellValue("." + viewName + ":" + rtuNum);
        tableBodyRow6.createCell(1).setCellValue(4);
        tableBodyRow6.createCell(2);
        tableBodyRow6.createCell(3).setCellValue("lt:2 LANG:10001 \"4 HoldingRegisters\" LANG:10004 \"4 HoldingRegisters\" ");
        tableBodyRow6.createCell(4).setCellValue("720896 0:0.0");
        tableBodyRow6.createCell(5).setCellValue(502);
        tableBodyRow6.createCell(6);
        tableBodyRow6.createCell(7);
        rowNum++;
        return rowNum;
    }
    
    public int generateTableBody(Sheet sheet,int rowNum,int start)
    {
        if((valList2.size() - start) < nameCount)
        {
            nameCount = valList2.size() - start;
            breakFlag = true;
        }
        for(int i = 0; i < nameCount ; i++)
        {
            
            Row tableBodyRow = sheet.createRow(rowNum);
            tableBodyRow.createCell(0).setCellValue("." + viewName + ":" + rtuNum + ".4");
            tableBodyRow.createCell(1).setCellValue(i + nameStart);
            tableBodyRow.createCell(2);
            tableBodyRow.createCell(3).setCellValue("lt:2 LANG:10001 \"New node 1\" LANG:10004 \"New node 1\" ");
//            tableBodyRow.createCell(4).setCellValue("720896 " + systemPrefix + ":" + (addressStart + i) + ".3");
            tableBodyRow.createCell(4).setCellValue("720896 " + systemPrefix + valList2.get(start + i) + suffix);
            tableBodyRow.createCell(5).setCellValue(2);
            tableBodyRow.createCell(6).setCellValue(bit16);
            tableBodyRow.createCell(7);
            rowNum++;
        }
        return rowNum;
    }
    
}
